package com.k.jdbc.utils;

import java.sql.*;
import java.util.*;

/**
 * jdbc operator based on connection
 * @author k
 */
public class SqlExecutor {

    private DatabaseTypeEnum databaseType;
    private String host;
    private Integer port;
    private String databaseInstance;
    private String username;
    private String password;
    private SqlExecutor(DatabaseTypeEnum databaseType,String host, Integer port,String databaseInstance,String user,String password){
        this.databaseType = databaseType;
        this.host = host;
        this.port = port;
        this.databaseInstance = databaseInstance;
        this.username = user;
        this.password = password;
    }

    public DatabaseTypeEnum getDatabaseType() {
        return databaseType;
    }

    public void setDatabaseType(DatabaseTypeEnum databaseType) {
        this.databaseType = databaseType;
    }

    public String getHost() {
        return host;
    }

    public void setHost(String host) {
        this.host = host;
    }

    public Integer getPort() {
        return port;
    }

    public void setPort(Integer port) {
        this.port = port;
    }

    public String getDatabaseInstance() {
        return databaseInstance;
    }

    public void setDatabaseInstance(String databaseInstance) {
        this.databaseInstance = databaseInstance;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public static Builder builder() {return new SqlExecutor.Builder();}

    public static class Builder {
        private DatabaseTypeEnum databaseType;
        private String host;
        private Integer port;
        private String databaseInstance;
        private String username;
        private String password;
        Builder() {}

        public SqlExecutor.Builder setDbType(DatabaseTypeEnum dbType) {
            this.databaseType = dbType;
            return this;
        }
        public SqlExecutor.Builder setDbHost(String host) {
            this.host = host;
            return this;
        }
        public SqlExecutor.Builder setDbPort(Integer port) {
            this.port = port;
            return this;
        }
        public SqlExecutor.Builder setDbInstance(String dbInstance) {
            this.databaseInstance = dbInstance;
            return this;
        }
        public SqlExecutor.Builder setDbUsername(String username) {
            this.username = username;
            return this;
        }
        public SqlExecutor.Builder setDbPassword(String password) {
            this.password = password;
            return this;
        }
        public SqlExecutor build() {
            return new SqlExecutor(this.databaseType, this.host,this.port,this.databaseInstance,this.username,this.password);
        }

        @Override
        public String toString() {
            return "Builder{" +
                    "databaseType=" + databaseType +
                    ", host='" + host + '\'' +
                    ", port=" + port +
                    ", databaseInstance='" + databaseInstance + '\'' +
                    ", username='" + username + '\'' +
                    ", password='" + password + '\'' +
                    '}';
        }
    }

    @Override
    public String toString() {
        return "SqlExecutor{" +
                "databaseType=" + databaseType +
                ", host='" + host + '\'' +
                ", port=" + port +
                ", databaseInstance='" + databaseInstance + '\'' +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }

    private JdbcConnEntity getJdbcConnEntity(String databaseInstance, String username, String password){
        String driver, url;
        String myInstance=null;
        String myUser=null;
        String myPass=null;
        if(databaseInstance != null){
            myInstance = databaseInstance;
        }
        if(username != null){
            myUser = username;
        }
        if(password != null){
            myPass = password;
        }
        switch (databaseType){
            case MYSQL:
                driver = "com.mysql.jdbc.Driver";
                url = "jdbc:mysql://" + host + ":" + port + "/" + myInstance;
                break;
            case POSTGRESQL:
                driver = "org.postgresql.Driver";
                url = "jdbc:postgresql://" + host + ":" + port + "/" + myInstance;
                break;
            case ORACLE:
                driver = "oracle.jdbc.driver.OracleDriver";
                url = "jdbc:oracle:thin:@"+ host +":"+ port +":"+ myInstance;
                break;
            case SQLSERVER:
                driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
                url = "jdbc:sqlserver://"+host+":"+port+";databaseName="+ myInstance;
                break;
            default:
                driver = "org.sqlite.JDBC";
//                url = "jdbc:sqlite::resource:static/sqllite/test.db";
                url = "jdbc:sqlite:c:/sqlite/test.db";
                break;
        }
        JdbcConnEntity entity = new JdbcConnEntity();
        entity.setDriverClassName(driver);
        entity.setJdbcUrl(url);
        entity.setUsername(myUser);
        entity.setPassword(myPass);
        return entity;
    }
    private void close(Connection conn){
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    private void close(ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    private void close(PreparedStatement pst){
        if(pst!=null){
            try {
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    private void close(Statement st){
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    private JdbcConnEntity getJdbcConnEntity(){
        return getJdbcConnEntity(null,null,null);
    }

    private Connection getConnection() throws ClassNotFoundException, SQLException {
        JdbcConnEntity c = getJdbcConnEntity(this.databaseInstance,this.username,this.password);
        Class.forName(c.getDriverClassName());
        return DriverManager.getConnection(c.getJdbcUrl(), c.getUsername(), c.getPassword());
    }

    private Connection getConnection(String databaseInstance,String username,String password) throws ClassNotFoundException, SQLException {
        JdbcConnEntity c = getJdbcConnEntity(databaseInstance,username,password);
        Class.forName(c.getDriverClassName());
        return DriverManager.getConnection(c.getJdbcUrl(), c.getUsername(), c.getPassword());
    }
    public List<Map<String,Object>> selectList(Connection conn, String sql){
        List<Map<String, Object>> list = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            list = convertResultSet(rs);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close(rs);
            close(st);
        }
        return list;
    }

    public List<Map<String,Object>> selectList(String sql){
        List<Map<String, Object>> list = null;
        Connection conn = null;
        try {
            conn = getConnection();
            list = selectList(conn,sql);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close(conn);
        }
        return list;
    }

    private static List<Map<String,Object>> convertResultSet(ResultSet rs) throws SQLException {
        List<Map<String, Object>> list = new ArrayList<>();
        ResultSetMetaData md = rs.getMetaData();
        int columnCount = md.getColumnCount();
        while(rs.next()){
            Map<String, Object> rowData = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnName(i), rs.getObject(i));
            }
            list.add(rowData);
        }
        return list;
    }

    public static String executeSql(Connection conn,String sql)  {
        String result = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try{
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            if(rs.next()){
                String str1 = rs.getString(1);
                result = str1;
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    public static String executeSqlList(Connection conn,List<String> sqlList)  {
        String result = null;
        String itmSql = "";
        Statement st = null;
        try{
            conn.setAutoCommit(false);
            for(String sql : sqlList){
                itmSql = sql;
                st = conn.createStatement();
                st.execute(sql);
            }
            conn.commit();
        }catch (Exception e){
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            result = itmSql + ":"+e.getMessage();
            e.printStackTrace();
        }finally {
            try {
                conn.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            if(st != null){
                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }


    public String executeSql(String username,String pwd,String databaseInstance,String sql) {
        String result = null;
        Connection conn = null;
        try{
            conn = getConnection(databaseInstance,username,pwd);
            result = executeSql(conn,sql);
        }catch (Exception e){
            result = sql+" Error: "+e.getMessage();
        }finally {
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    /**
     * init postgres
     * @param createDatabaseInstance database instance
     * @param createDatabaseUser user
     * @param createDatabasePassword password
     * @return null success
     */
    public String initDatabasePostgres(String createDatabaseInstance,
                                     String createDatabaseUser,
                                     String createDatabasePassword){
        String result = null;
        String[] sqlArr = new String[5];
        sqlArr[0] = "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname=" + "'" + createDatabaseInstance + "'" + " AND pid<>pg_backend_pid() " + "\n";
        sqlArr[1] = "drop database if exists " + createDatabaseInstance + "\n";
        sqlArr[2] = "drop ROLE if exists " + createDatabaseUser + "\n";
        sqlArr[3] = "CREATE ROLE " + createDatabaseUser + " LOGIN ENCRYPTED PASSWORD " + "'" + createDatabasePassword + "'" + " SUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION" + "\n";
        sqlArr[4] = "create database " + createDatabaseInstance + " ENCODING=UTF8" + " TEMPLATE=template0" + "\n";
        Connection conn = null;
        try {
            conn = getConnection();
            result = executeSqlList(conn, Arrays.asList(sqlArr));
        } catch (ClassNotFoundException e) {
            result = "ClassNotFoundException:" + e.getMessage();
            e.printStackTrace();
        } catch (SQLException e) {
            result = "SQLException:" + e.getMessage();
            e.printStackTrace();
        }finally {
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }

    public boolean isExistDatabaseForPostgres(String databaseSuperInstance,String databaseSuperUsername,String databaseSuperPassword,String dbName){
        String result = null;
        // postgres
        String queryDatabaseSql = "SELECT u.datname  FROM pg_catalog.pg_database u where u.datname=" + "'" + dbName + "'";
        String mysqlQueryDatabaseInstance = "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA t where t.SCHEMA_NAME = "+dbName+"";
        Connection conn = null;
        try {
            conn = getConnection(databaseSuperInstance,databaseSuperUsername,databaseSuperPassword);
            result = executeSql(conn,queryDatabaseSql);
        } catch (ClassNotFoundException e) {
            result = "ClassNotFoundException:" + e.getMessage();
            e.printStackTrace();
        } catch (SQLException e) {
            result = "SQLException:" + e.getMessage();
            e.printStackTrace();
        }finally {
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return result==null?false:true;
    }


    /**
     * init mysql
     * @param createDbInstance db instance
     * @param createUser user
     * @param createPassword password
     * @return  null success
     */
    public String initMysql(String createDbInstance,String createUser,String createPassword) {
        String result = null;
        Connection conn = null;
        if("mysql".equalsIgnoreCase(createDbInstance) ||
                "information_schema".equalsIgnoreCase(createDbInstance) ||
                "performance_schema".equalsIgnoreCase(createDbInstance) ||
                "test".equalsIgnoreCase(createDbInstance)){
            return "Can not create system database";
        }
        if("root".equalsIgnoreCase(createUser)){
            return "Can not create system user";
        }
        try {
            conn = this.getConnection();
            String countStr = executeSql(conn," select count(*) from mysql.user t where t.`User` = '"+createUser+"'; ");
            Integer userCount = 0;
            if(countStr!=null){
                userCount = Integer.parseInt(countStr);
            }
            List<String > sqlList = new ArrayList<>();

            String sql11 = " DROP DATABASE IF EXISTS " +createDbInstance+ "; ";
            String sql12 = " CREATE DATABASE IF NOT EXISTS "+createDbInstance+" DEFAULT CHARSET utf8 COLLATE utf8_general_ci; ";
            sqlList.add(sql11);
            sqlList.add(sql12);
            String sql21;
            String sql23;
            if(userCount>0){
                sql21 = " DROP USER '"+createUser+"'@'%'; ";
                sql23 = " DROP USER '"+createUser+"'@'localhost'; ";
                sqlList.add(sql21);
                sqlList.add(sql23);
            }
            String sql22 = " CREATE USER '"+createUser+"'@'%' IDENTIFIED BY '"+createPassword+"'; ";
            String sql24 = " CREATE USER '"+createUser+"'@'localhost' IDENTIFIED BY '"+createPassword+"'; ";

            String sql31 = " GRANT ALL PRIVILEGES ON *.* TO '"+createUser+"'@'%' IDENTIFIED BY '"+createPassword+"' WITH GRANT OPTION; ";
            String sql32 = " GRANT ALL PRIVILEGES ON *.* TO '"+createUser+"'@'localhost' IDENTIFIED BY '"+createPassword+"' WITH GRANT OPTION; ";
            String sql4 = " flush privileges;  ";

            sqlList.add(sql22);
            sqlList.add(sql24);
            sqlList.add(sql31);
            sqlList.add(sql32);
            sqlList.add(sql4);
            String exeSqlResult = executeSqlList(conn,sqlList);
            if(exeSqlResult!=null){
                result = "Execute SQL error:" +exeSqlResult;
            }
        } catch (ClassNotFoundException e) {
            result = "ClassNotFoundException:"+e.getMessage();
            e.printStackTrace();
        } catch (SQLException e) {
            result = "SQLException:"+e.getMessage();
            e.printStackTrace();
        }finally {
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return result;
    }
}
